﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TianShiProject.EntityFrameworkCore;
using TianShiProject.Models.Common;
using TianShiProject.Models.Shen.GeneralContracts;
using Volo.Abp.EntityFrameworkCore;

namespace TianShiProject.Shen.GeneralContracts
{
    public class GeneralContractShenRepository : DapperDbContextRepository, IGeneralContractShenRepository
    {
        public GeneralContractShenRepository(IDbContextProvider<TianShiProjectDbContext> dbContextProvider) : base(dbContextProvider)
        {
        }
        /// <summary>
        /// 提交审批
        /// </summary>
        /// <param name="generalContractShenDto"></param>
        /// <param name="loginname"></param>
        /// <param name="loginid"></param>
        /// <returns></returns>
        public async Task<int> PostAddGeneralContract(GeneralContractShenDto generalContractShenDto, string loginname, int? loginid)
        {
            //提交审批单
            //return await ExecuteAsync
            //    ($"insert into GeneralContract values('{generalContractShenDto.TheShipContractNO}','{generalContractShenDto.TheContractTitle}','{generalContractShenDto.TheOwnerUnit}','{generalContractShenDto.FreightPrincipal}','{generalContractShenDto.Line}','{generalContractShenDto.TonsOfFreight}','{generalContractShenDto.ConditionTonnage}', '{generalContractShenDto.CharterValue}','{generalContractShenDto.Agent}', '{generalContractShenDto.SignedTime.ToString("yyyy/MM/dd")}', '{generalContractShenDto.TheContractAmount}', '{generalContractShenDto.ProjectDescriptio}', '{generalContractShenDto.ChangeTheTerms}', '{generalContractShenDto.ContractTheText}',0, '{generalContractShenDto.TheApprover}', '{generalContractShenDto.Remark}',0)", generalContractShenDto);
            //申请表主键
            var shenId = Guid.NewGuid();
            //申请表主键赋值
            generalContractShenDto.Id = shenId;
            //实例化流程实例表
            ProcedureListShenDto procedureList = new ProcedureListShenDto();
            //流程实例表主键
            var liuId = Guid.NewGuid();
            //流程实例表主键赋值
            procedureList.Id = liuId;
            //流程实例表申请表外键赋值
            procedureList.ShenId = shenId;
            //流程实例表状态
            procedureList.ProcedureState = 0;
            //流程实例表发起人名
            procedureList.BeginName = loginname;
            //流程发起人Id
            procedureList.BeginId = (int)loginid;
            //流程实例表获取发起角色外键
            var beginRoloId = await ExecuteScalarAsync($"select c.Id from ORRelation a join Operator b on a.OperatorId=b.OperatorNumber join Role c on a.RoleId=c.RoleNumber where b.Id='{loginid}'", null);
            //流程实例表获取发起角色名
            var beginRoloName = await ExecuteScalarAsync($"select c.RoleName from ORRelation a join Operator b on a.OperatorId=b.OperatorNumber join Role c on a.RoleId=c.RoleNumber where b.Id='{loginid}'", null);
            //流程实例表获取当前角色外键
            var dangRoloId = await ExecuteScalarAsync($"select  c.Id from ORRelation a join Operator b on a.OperatorId=b.OperatorNumber join Role c on a.RoleId=c.RoleNumber where b.Id='{loginid}'", null);
            //流程实例表获取当前角色名
            var dangRoloName = await ExecuteScalarAsync($"select c.RoleName from ORRelation a join Operator b on a.OperatorId=b.OperatorNumber join Role c on a.RoleId=c.RoleNumber where b.Id='{loginid}'", null);
            //流程实例表节点外键
            var jiedianId = await ExecuteScalarAsync($"select a.NodeNumber from ProcedureNodeShen a join Role b on a.CaoId=b.Id where b.Id='{dangRoloId}'", null);
            var jiedianName = await ExecuteScalarAsync($"select a.NodeName from ProcedureNodeShen a join Role b on a.CaoId=b.Id where b.Id='{dangRoloId}'", null);
            //当前操作人名
            procedureList.CaoName = dangRoloName.ToString();
            //当前操作人Id
            procedureList.CaoId = (int)dangRoloId;
            //下一节点
            var nextNode = await ExecuteScalarAsync($"select a.NextNodeNumber from ProcedureNodeShen a join Role b on a.CaoId=b.Id where b.Id='{dangRoloId}'", null);
            //下一操作角色名
            var nextRoleName = await ExecuteScalarAsync($"select b.RoleName from ProcedureNodeShen a join Role b on a.CaoId=b.Id where a.NodeNumber='{nextNode}'", null);
            //下一操作角色Id
            var nextRoleId = await ExecuteScalarAsync($"select b.Id from ProcedureNodeShen a join Role b on a.CaoId=b.Id where a.NodeNumber='{nextNode}'", null);
            //操作时间
            procedureList.CreateTime = DateTime.Now;
            //实例化流程记录表
            ProcedureLogShenDto procedureLog = new ProcedureLogShenDto();
            //记录表主键
            var logId = Guid.NewGuid();
            procedureLog.Id = logId;
            //记录表流程实例外键
            procedureLog.ProcedureId = liuId;
            //记录表节点编号
            var logNodeNumber = await ExecuteScalarAsync($"select a.NodeNumber from ProcedureNodeShen a join Role b on a.CaoId=b.Id where a.CaoId='{dangRoloId}'", null);
            //记录表节点Id
            var logNodeName = await ExecuteScalarAsync($"select a.NodeName from ProcedureNodeShen a join Role b on a.CaoId=b.Id where a.CaoId='{dangRoloId}'", null);
            //记录表当前操作人名
            procedureLog.CaoName = dangRoloName.ToString();
            //记录表当前操作Id
            procedureLog.CaoId = (int)dangRoloId;
            //记录表当前角色名
            //var logRoleName = await ExecuteScalarAsync($"select c.RoleName from ORRelation a join Operator b on a.OperatorId=b.OperatorNumber join Role c on a.RoleId=c.RoleNumber where c.RoleNumber='{dangRoloId}'", null);
            //记录表当前角色Id
            //var logRoleId = await ExecuteScalarAsync($"select c.RoleNumber from ORRelation a join Operator b on a.OperatorId=b.OperatorNumber join Role c on a.RoleId=c.RoleNumber where c.RoleNumber='{dangRoloId}'", null);
            //记录表操作时间
            procedureLog.UpdateTime = DateTime.Now;
            //记录表是否已读
            procedureLog.IsRead = 1;
            //记录表是否已通过
            procedureLog.IsTong = 1;
            try
            {
                //添加申请表
                await ExecuteAsync
            ($"insert into GeneralContractShen values('{generalContractShenDto.Id}','{generalContractShenDto.GeneralContractNos}','{generalContractShenDto.Thecontracttitles}','{generalContractShenDto.TheOtherUnits}','{generalContractShenDto.TheOtherPartyHead}','{generalContractShenDto.ContractCategory}','{generalContractShenDto.ContractAmounts}','{generalContractShenDto.Agents}', '{generalContractShenDto.SignedTimes.ToString("yyyy/MM/dd")}', '{generalContractShenDto.TheContractAmount}', '{generalContractShenDto.ProjectDescriptios}', '{generalContractShenDto.ThisMainClauses}', '{generalContractShenDto.ContractTexts}', 0, '{generalContractShenDto.TheApprovers}', '{generalContractShenDto.Remark}',0)", generalContractShenDto);
                //添加实例表
                await ExecuteAsync
            ($"insert into ProcedureListShen values('{procedureList.Id}','{jiedianId}','{jiedianName}','{procedureList.ProcedureState}','{beginRoloId}','{beginRoloName}','{procedureList.CaoId}', '{procedureList.CaoName}','{nextRoleId}','{nextRoleName}','{dangRoloName}', '{procedureList.CreateTime.ToString("yyyy/MM/dd")}', '{procedureList.UpdateTime.ToString("yyyy/MM/dd")}', '{ procedureList.ShenId}',0)", procedureList);
                //添加记录表
                await ExecuteAsync
            ($"insert into ProcedureLogShen values('{procedureLog.Id}','{procedureLog.ProcedureId}','{logNodeNumber}','{logNodeName}','{procedureLog.CaoId}','{procedureLog.CaoName}','{procedureList.UpdateTime.ToString("yyyy/MM/dd")}','{procedureLog.IsRead}', '{procedureLog.IsTong}',0)", procedureLog);
                return 1;
            }
            catch (Exception)
            {

                throw;
            }
        }
        /// <summary>
        /// 显示审批
        /// </summary>
        /// <param name="title"></param>
        /// <param name="danwei"></param>
        /// <param name="fuzeren"></param>
        /// <param name="jingbanren"></param>
        /// <param name="qianding"></param>
        /// <returns></returns>
        public async Task<PageResponse<GeneralContractShenDto>> GetShowGeneralContract(string title, string danwei, string fuzeren, string jingbanren, DateTime? qianding, int pageindex, int pagesize)
        {
            PageResponse<GeneralContractShenDto> response = new PageResponse<GeneralContractShenDto>();
            var sql = $"select a.*,c.ProcedureState,c.CaoName from GeneralContractShen a join ProcedureListShen c on a.Id=c.ShenId where a.IsDelete=0  ";
            if (!string.IsNullOrWhiteSpace(title))
            {
                sql += $" and a.Thecontracttitles like '%" + title + "%'";
            }
            if (qianding != null)
            {
                sql += $" and a.SignedTimes='{qianding}'";
            }
            if (!string.IsNullOrWhiteSpace(danwei))
            {
                sql += $" and a.TheOtherUnits='{danwei}'";
            }
            if (!string.IsNullOrWhiteSpace(fuzeren))
            {
                sql += $" and a.TheOtherPartyHead='{fuzeren}'";
            }
            if (!string.IsNullOrWhiteSpace(jingbanren))
            {
                sql += $" and a.Agents='{jingbanren}'";
            }
            var list = await this.QueryAsync<GeneralContractShenDto>(sql, null);
            response.PageTotal = list.Count;
            sql += $"order by Id offset {(pageindex - 1) * pagesize} rows fetch next {pagesize} rows only";
            var lists = await this.QueryAsync<GeneralContractShenDto>(sql, null);
            response.PageData = lists;
            return response;
        }
        /// <summary>
        /// 待审批显示
        /// </summary>
        /// <param name="title"></param>
        /// <param name="danwei"></param>
        /// <param name="fuzeren"></param>
        /// <param name="jingbanren"></param>
        /// <param name="qianding"></param>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public async Task<PageResponse<GeneralContractShenDto>> GetDaiGeneralContract(string title, string danwei, string fuzeren, string jingbanren, DateTime? qianding, int pageindex, int pagesize)
        {
            PageResponse<GeneralContractShenDto> response = new PageResponse<GeneralContractShenDto>();
            var sql = $"select a.*,c.ProcedureState,c.CaoName from GeneralContractShen a join ProcedureListShen c on a.Id=c.ShenId where a.IsDelete=0 and  c.ProcedureState=0";
            if (!string.IsNullOrWhiteSpace(title))
            {
                sql += $" and a.Thecontracttitles like '%" + title + "%'";
            }
            if (qianding != null)
            {
                sql += $" and a.SignedTimes='{qianding}'";
            }
            if (!string.IsNullOrWhiteSpace(danwei))
            {
                sql += $" and a.TheOtherUnits='{danwei}'";
            }
            if (!string.IsNullOrWhiteSpace(fuzeren))
            {
                sql += $" and a.TheOtherPartyHead='{fuzeren}'";
            }
            if (!string.IsNullOrWhiteSpace(jingbanren))
            {
                sql += $" and a.Agents='{jingbanren}'";
            }
            var list = await this.QueryAsync<GeneralContractShenDto>(sql, null);
            response.PageTotal = list.Count;
            sql += $"order by Id offset {(pageindex - 1) * pagesize} rows fetch next {pagesize} rows only";
            var lists = await this.QueryAsync<GeneralContractShenDto>(sql, null);
            response.PageData = lists;
            return response;
        }
        /// <summary>
        /// 已审批显示
        /// </summary>
        /// <param name="title"></param>
        /// <param name="danwei"></param>
        /// <param name="fuzeren"></param>
        /// <param name="jingbanren"></param>
        /// <param name="qianding"></param>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public async Task<PageResponse<GeneralContractShenDto>> GetYiGeneralContract(string title, string danwei, string fuzeren, string jingbanren, DateTime? qianding, int pageindex, int pagesize)
        {
            PageResponse<GeneralContractShenDto> response = new PageResponse<GeneralContractShenDto>();
            var sql = $"select a.*,c.ProcedureState,c.CaoName from GeneralContractShen a join ProcedureListShen c on a.Id=c.ShenId where a.IsDelete=0 and  c.ProcedureState not in(0)";
            if (!string.IsNullOrWhiteSpace(title))
            {
                sql += $" and a.Thecontracttitles like '%" + title + "%'";
            }
            if (qianding != null)
            {
                sql += $" and a.SignedTimes='{qianding}'";
            }
            if (!string.IsNullOrWhiteSpace(danwei))
            {
                sql += $" and a.TheOtherUnits='{danwei}'";
            }
            if (!string.IsNullOrWhiteSpace(fuzeren))
            {
                sql += $" and a.TheOtherPartyHead='{fuzeren}'";
            }
            if (!string.IsNullOrWhiteSpace(jingbanren))
            {
                sql += $" and a.Agents='{jingbanren}'";
            }
            var list = await this.QueryAsync<GeneralContractShenDto>(sql, null);
            response.PageTotal = list.Count;
            sql += $"order by Id offset {(pageindex - 1) * pagesize} rows fetch next {pagesize} rows only";
            var lists = await this.QueryAsync<GeneralContractShenDto>(sql, null);
            response.PageData = lists;
            return response;
        }
        /// <summary>
        /// 审批通过
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<int> PutTongGeneralContract(Guid id)
        {
            var sql = $"update ProcedureListShen set ProcedureState=1 where ShenId='{id}'";
            return await ExecuteAsync(sql, null);
        }
        /// <summary>
        /// 审批拒绝
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<int> PutJuGeneralContract(Guid id)
        {
            var sql = $"update ProcedureListShen set ProcedureState=2 where ShenId='{id}'";
            return await ExecuteAsync(sql, null);
        }
    }
}
